package com.aier.admin_new.service.impl;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.aier.admin_new.entity.NameingBean;
import com.aier.admin_new.service.IClassService;
import com.aier.classes.entity.ClassPhoto;
import com.aier.classes.entity.ClassPhotoImg;
import com.aier.classes.entity.ClassPhotoType;
import com.aier.classes.entity.ClassTeacher;
import com.aier.classes.entity.Classes;
import com.aier.classes.entity.mapper.ClassPhotoImgRowMapper;
import com.aier.classes.entity.mapper.ClassPhotoRowMapper;
import com.aier.classes.entity.mapper.ClassPhotoTypeRowMapper;
import com.aier.classes.entity.mapper.ClassTeacherRowMapper;
import com.aier.classes.entity.mapper.ClassesRowMapper;
import com.aier.common.common.SystemDateFormatUtil;
import com.aier.common.dao.ICommonDAO;
import com.aier.school.entity.School;
import com.aier.school.entity.mapper.SchoolRowMapper;
import com.aier.user.entity.CallRoll;
import com.aier.user.entity.CallRollClass;
import com.aier.user.entity.CallRollSchool;
import com.aier.user.entity.mapper.CallRollRowMapper;
import com.aier.user.entity.mapper.CallRollClassRowMapper;
import com.aier.user.entity.mapper.CallRollSchoolRowMapper;
import com.aier.user.entity.mapper.UserPhotoImgRowMapper;

@Component("newClassService")
public class ClassServiceImpl_J implements IClassService{
    @Resource(name = "commonDao")
    private ICommonDAO commonDao;

    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }

    @Override
    public int countClassByName(String cname,int sid,Long cid) {
        String sql = " from classes where cname=? and sid=? ";
        if(cid!=null){
            sql = sql + " and cid!=" + cid;
        }
        return commonDao.countByQuali(sql, new Object[] { cname,sid});
    }

    @Override
    public Classes searchClassById(Long cid){
        return (Classes) commonDao.listByObject(
                "select c.*,count(DISTINCT cs.csid) as num_student,count(DISTINCT cp.cpid) as num_parent," +
                "count(DISTINCT ct.ctid) as num_teacher from classes c left JOIN class_student cs on c.cid=cs.cid " +
                "left JOIN class_parent cp on c.cid=cp.cid left JOIN class_teacher ct on c.cid=ct.cid " +
                "WHERE c.cid=? GROUP BY c.cid", new Object[] { cid },
                new ClassesRowMapper(ClassesRowMapper.AND_USERNUMS));        
    }
    
    @Override
    public School searchSchoolByCid(Long cid) {
        return (School) commonDao.listByObject("select * from school where sid=" +
        		"(select sid from classes where cid=?)", new Object[]{cid}, new SchoolRowMapper());
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<Classes> searchClassListByUid(Long uid){
        return (List<Classes>) commonDao.list("select * from classes where cid in (select cid from class_parent where uid=? " +
                "UNION select ct.cid from class_teacher ct INNER JOIN school_teacher st on ct.tid=st.tid where st.uid=?)", 
                new Object[] { uid,uid },new ClassesRowMapper());
    }
    
    @Override
    public Map<String, Object> searchClassList(int page,int pagesize, Integer sid,Long cid){
        String sql = " from classes c where sid=? ";
        if(cid!=null&&cid!=-1){
            sql = sql + " and cid=" + cid;
        }
        String sql_first = sql;
        return commonDao.queryPage(
            "select c.*,(select group_concat((select tname from school_teacher where tid=ct.tid)) from class_teacher ct " +
            "where ct.cid =c.cid) as tnames,(select count(DISTINCT cs.csid) from class_student cs where cs.cid = c.cid) as num_student " 
                    + sql, "select count(*) " + sql_first, page,
            pagesize, new Object[] { sid }, new Object[] { sid },
            new ClassesRowMapper(ClassesRowMapper.AND_TEACHERNAMES));  
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<Classes> searchClassList(Integer sid){
        return (List<Classes>) commonDao.list("select c.* from classes c where c.sid=? ", 
                new Object[] { sid },new ClassesRowMapper());
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<Classes> searchClassList_Num(Integer sid){
        return (List<Classes>) commonDao.list("select c.*,count(DISTINCT cs.csid) as num_student,count(DISTINCT cp.cpid) as num_parent," +
                "count(DISTINCT ct.ctid) as num_teacher from classes c left JOIN class_student cs on c.cid=cs.cid " +
                "left JOIN class_parent cp on c.cid=cp.cid left JOIN class_teacher ct on c.cid=ct.cid WHERE sid=? GROUP BY c.cid", 
                new Object[] { sid },new ClassesRowMapper(ClassesRowMapper.AND_USERNUMS));        
    }
    
    @Override
    public int insertClass(Classes classes) {
        KeyHolder kh = commonDao.insert(
                ClassesRowMapper.getPreparedStatementCreator(classes),
                new GeneratedKeyHolder());
        if(kh!=null){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int insertClass(Classes classes, String teacheres){
        Timestamp ctime = new Timestamp(new Date().getTime());
        KeyHolder kh = commonDao.insert(
                ClassesRowMapper.getPreparedStatementCreator(classes),
                new GeneratedKeyHolder());
        if(kh!=null){
            if(!"".equals(teacheres)){
                for(String tid:teacheres.split(",")){
                    commonDao.insert(
                            ClassTeacherRowMapper.getPreparedStatementCreator(
                                    new ClassTeacher(null, Long.valueOf(tid),kh.getKey().longValue() , 
                                            ctime, CLASS_TEACHER_ISADMIN_YES)),
                            new GeneratedKeyHolder());
                }
            }    
            return 1;
        }else{
            return 0;
        }
            
    }

    
    @Override
    public int deleteClass(Long cid) {
        if (commonDao.update("delete from classes where cid=?",
                new Object[] {cid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateClassLogo(Long cid, String clogo) {
        if (commonDao.update("update classes set clogo=? where cid=?",
                new Object[] { clogo,cid}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }
    
    @Override
    public int updateClassImg(Long cid, String img) {
        if (commonDao.update("update classes set img=? where cid=?",
                new Object[] { img,cid}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateClassInfo(Classes classes) {
        if (commonDao.update("update classes set cname=?,desinfo=?,manifesto=?,img=? where cid=?",
                new Object[] { classes.getCname(),classes.getDesinfo(),classes.getManifesto(),classes.getImg(),classes.getCid()}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

//=====================================================================================    
    
    @Override
    public Map<String, Object> searchClassPhotoList(int page, int pagesize,Long cid) {
        String sql = " from class_photo where cid=? order by cpid desc";
        String sql_first = sql;
        return commonDao.queryPage(
            "select * " + sql, "select count(*) " + sql_first, page,
            pagesize, new Object[] { cid }, new Object[] { cid },
            new UserPhotoImgRowMapper());  
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassPhoto> searchClassPhotoList(Long cid){
        return (List<ClassPhoto>) commonDao.list("select * from class_photo where cid=? order by cpid desc", 
                new Object[] { cid },new ClassPhotoRowMapper());
    }
    
    @Override
    public long insertClassPhoto(ClassPhoto cp) {
        KeyHolder kh = commonDao.insert(
                ClassPhotoRowMapper.getPreparedStatementCreator(cp),
                new GeneratedKeyHolder());
        if(kh!=null){
            return kh.getKey().longValue();
        }else{
            return 0;
        }
    }

    @Override
    public int updateClassPhoto(ClassPhoto cp) {
        if (commonDao.update("update class_photo set img=?,desinfo=? where cpid=?",
                new Object[] { cp.getImg(),cp.getDesinfo(),cp.getCpid()}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int deleteClassPhoto(Long cpid) {
        if (commonDao.update("delete from class_photo where cpid=?",
                new Object[] {cpid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int insertClassPhotoType(ClassPhotoType cpt) {
        KeyHolder kh = commonDao.insert(
                ClassPhotoTypeRowMapper.getPreparedStatementCreator(cpt),
                new GeneratedKeyHolder());
        if(kh!=null){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public long insertClassPhotoImg(ClassPhotoImg cpi) {
        KeyHolder kh = commonDao.insert(
                ClassPhotoImgRowMapper.getPreparedStatementCreator(cpi),
                new GeneratedKeyHolder());
        if(kh!=null){
            return kh.getKey().longValue();
        }else{
            return (long)0;
        }
    }

    @Override
    public int updateClassPhotoImg_face(Long cpiid) {
        int s = 0;
        ClassPhotoImg cpi = (ClassPhotoImg) commonDao.listByObject("select * from class_photo_img where cpiid=?", 
                new Object[]{cpiid}, new ClassPhotoImgRowMapper());
        if(cpi!=null){
            if (commonDao.update("update class_photo_type set img=? where cptid=?",
                    new Object[] {cpi.getImg(),cpi.getCptid() }) > 0) {
                s = 1;
            } 
        }
        return s;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassPhotoType> searchClassPhotoTypeList(Long cid) {
        return (List<ClassPhotoType>) commonDao.list("select cpt.*,count(cpi.cpiid) as num_img  from class_photo_type cpt " +
                "LEFT JOIN class_photo_img cpi on cpt.cptid=cpi.cptid  where cid=? GROUP BY cptid",
                new Object[] { cid },new ClassPhotoTypeRowMapper(ClassPhotoTypeRowMapper.AND_NUM));
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<ClassPhotoImg> searchClassPhotoImgList(Long cptid) {
        return (List<ClassPhotoImg>) commonDao.list("select *  from class_photo_img where cptid=? order by cpiid desc",
                new Object[] { cptid },new ClassPhotoImgRowMapper());
    }

    @Override
    public ClassPhotoImg searchClassPhotoImg(Long cpiid) {
        return (ClassPhotoImg) commonDao.listByObject("select * from class_photo_img where cpiid=?", 
                new Object[] {cpiid}, new ClassPhotoImgRowMapper());
    }

    @Override
    public int deleteClassPhotoImg(Long cpiid) {
        if (commonDao.update("delete from class_photo_img where cpiid=?",
                new Object[] {cpiid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int updateClassPhotoTypeCover(String img, Long cptid) {
        if (commonDao.update("update class_photo_type set img=? where cptid=?",
                new Object[] { img,cptid}) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public ClassPhotoType searchClassPhotoType(Long cptid) {
        return (ClassPhotoType) commonDao.listByObject("select * from class_photo_type where cptid=?", 
                new Object[] {cptid}, new ClassPhotoTypeRowMapper());
    }

    @Override
    public int insertCallRoll(Long cid,List<NameingBean> crList) {
        
        Timestamp cdate = new Timestamp(new Date().getTime());
        String dateStr = SystemDateFormatUtil.formatDate(cdate, SystemDateFormatUtil.FORMAT_DATE);
        
        @SuppressWarnings("unchecked")
        List<CallRollClass> callRollClassList = (List<CallRollClass>) commonDao.list("select * from call_roll_class where cid = ? and cdate = ?",
                new Object[]{cid,dateStr},new CallRollClassRowMapper());
        // 以点过名,直接返回
        if (callRollClassList.size() > 0) {
            return 0;
        }
        int temp=0;
        // 新增点名记录
        for (int i=0;i<crList.size();i++) {
        	NameingBean mb= crList.get(i);
        	if(mb.getState()!="0")
        	{
        		temp++;
        	}
            commonDao.update("insert into call_roll(cid,csid,state,cdate) values(?,?,?,?)",new Object[]{cid,mb.getCsid(),mb.getState(),dateStr});
        }
        // 新增班级点名统计
        commonDao.update("insert into call_roll_class(cid,count,cdate) values(?,?,?)",new Object[]{cid,temp,dateStr});
        
        // 新增学校点名统计
        CallRollSchool crs = (CallRollSchool) commonDao.listByObject("select crs.* from call_roll_school crs left join classes c " +
        		"on crs.sid = c.sid where c.cid = ? and crs.cdate = ? ", new Object[]{cid,dateStr},new CallRollSchoolRowMapper());
        if (crs != null) {
            if (crList.size() > 0) {
                commonDao.update("update call_roll_school set count = count+? where crsid = ?",new Object[]{temp,crs.getCrsid()});
            }
        } else {
            Classes c = (Classes) commonDao.listByObject("select * from classes where cid = ?", new Object[]{cid},new ClassesRowMapper());
            if (crList.size() > 0) {
                commonDao.update("insert into call_roll_school(sid,count,cdate) values (?,?,?)",new Object[]{c.getSid(),temp,dateStr});
            } else {
                commonDao.update("insert into call_roll_school(sid,count,cdate) values (?,?,?)",new Object[]{c.getSid(),0,dateStr});
            }
        }
        return 1;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<CallRollClass> searchCRSByDay(Long cid,String month) {
        return (List<CallRollClass>) commonDao.list("select * from call_roll_class where cid = ? and left(cdate,7) = ? order by cdate",
                new Object[]{cid,month},new CallRollClassRowMapper());
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<CallRoll> searchCRByDay(Long cid, String day) {
        return (List<CallRoll>) commonDao.list("select cr.*,cs.sname from call_roll cr left join class_student cs on cr.csid = cs.csid " +
        		"where cr.cid = ? and cr.cdate = ?",
                new Object[]{cid,day},new CallRollRowMapper());
    }


    @SuppressWarnings("unchecked")
    @Override
    public List<CallRollSchool> searchCRSByDayAllClass(Integer sid,String month) {
        return (List<CallRollSchool>) commonDao.list("select * from call_roll_school where sid = ? and left(cdate,7) = ? order by cdate",
                new Object[]{sid,month},new CallRollSchoolRowMapper());
        
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<CallRollClass> searchCRCByDayAllClass(Integer sid, String day) {
        return (List<CallRollClass>) commonDao.list("select crc.*,c.cname from call_roll_class crc left join classes c on crc.cid = c.cid " +
        		"where c.sid = ? and crc.cdate = ? and crc.count > 0 order by crc.count desc",
                new Object[]{sid,day},new CallRollClassRowMapper(CallRollClassRowMapper.AND_CNAME));
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<CallRollClass> searchCRCByMonthAllClass(Integer sid, String month) {
        return (List<CallRollClass>) commonDao.list("select crc.*,c.cname,sum(crc.count) as mcount from call_roll_class crc left join classes c " +
        		"on crc.cid = c.cid where c.sid = ? and crc.cdate = ? group by crc.cid order by mcount desc",
                new Object[]{sid,month},new CallRollClassRowMapper(CallRollClassRowMapper.AND_MCOUNT));
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<CallRoll> searchCRByMonth(Long cid, String month) {
        return (List<CallRoll>) commonDao.list("select *,sum(if(cr.state = 1,1,0)) as leav ,sum(if(cr.state = 2,1,0)) as absent " +
        		"from call_roll cr where cr.cid = ? and cr.cdate = ? group by cr.csid order by cr.cdate ",
                new Object[]{cid,month},new CallRollRowMapper(CallRollRowMapper.AND_COUNT));
    }

}
